﻿using AutoMapper;
using DocumentFormat.OpenXml.Spreadsheet;
using Magicodes.ExporterAndImporter.Core.Extension;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NP.BPMReportPlatform.Entities.BPM_Business.CommonReportConfigs;
using NP.BPMReportPlatform.Reports.CommonReportConfigs.Dtos;
using NP.BPMReportPlatform.Utilities;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Text;

namespace NP.BPMReportPlatform.Reports.CommonReportConfigs
{
    public class ImportCommonReportDataHelper : IDisposable
    {
        private ExcelWorksheet sheet { get; set; }
        public ImportCommonReportDataHelper(Stream file)
        {
            var excelPackage = new ExcelPackage(file);
            if (excelPackage.Workbook != null && excelPackage.Workbook.Worksheets.Count > 0)
            {
                sheet = excelPackage.Workbook.Worksheets.First();
            }
            else
            {
                throw new Exception("read excel failed.");
            }
        }

        public IEnumerable<List<CommonReportFieldConfig>> ConvertSheetToList(List<CommonReportFieldConfig> configFields, List<CommonReportField> fields,
            Entities.BPM_Platform.Users user)
        {
            try
            {
                var list = new List<List<CommonReportFieldConfig>>();
                var columnCount = sheet.Dimension.End.Column;

                var endRow = 2;
                for (var row = 2; row <= sheet.Dimension.Rows; row++)
                {
                    var columnValues = new List<string>();
                    for (var i = 1; i <= columnCount; i++)
                    {
                        var val = sheet.Cells[row, i];
                        if (val != null)
                        {
                            columnValues.Add(val.Text);
                        }
                    }

                    if (columnValues.All(string.IsNullOrWhiteSpace))
                    {
                        endRow = row - 1;
                        break;
                    }

                    endRow = row;
                }

                var selectItemDic = new Dictionary<string, List<KeyValuePair<string, string>>>();
                configFields.ForEach(item =>
                {
                    if (item.InputBoxType == "select-single" || item.InputBoxType == "select-multiple")
                    {
                        var list = ConvertExtension.ConvertJsonToList(item.ShortSelectItemJson);
                        if (list.Any())
                        {
                            selectItemDic.Add(item.FieldName, list);
                        }
                    }
                });

                for (var i = 2; i <= endRow; i++)
                {
                    var dots = new List<CommonReportFieldConfig>();
                    var configFieldList = JsonConvert.DeserializeObject<List<CommonReportFieldConfig>>(JsonConvert.SerializeObject(configFields));
                    var colIndex = 1;
                    foreach (var configField in configFieldList)
                    {
                        var field = fields.FirstOrDefault(x => x.COLUMN_NAME == configField.FieldName);
                        if (field == null)
                        {
                            throw new Exception($"第{i}行，{configField.FieldDesc}，没有对应的字段");
                        }
                        if (configField.IsRequired && string.IsNullOrWhiteSpace(sheet.Cells[i, colIndex]?.Value.ObjToString()))
                        {
                            throw new Exception($"第{i}行，{configField.FieldDesc}，请填写必填项");
                        }

                        if (configField.DefaultType > 0 && string.IsNullOrWhiteSpace(sheet.Cells[i, colIndex]?.Value?.ObjToString()))
                        {
                            configField.FieldValue = CommonFieldHelper.GetDefaultValue(
                                configField.DefaultType.Value, configField.DefaultValue, field.DATA_TYPE, field.COLUMN_DEFAULT, user,
                                configField.InputBoxType == "date" ? configField.ShortSelectItemJson : "");
                        }
                        else if (configField.InputBoxType == "select-single" && selectItemDic.ContainsKey(configField.FieldName))
                        {
                            var label = sheet.Cells[i, colIndex]?.Value.ObjToString();
                            if (!string.IsNullOrWhiteSpace(label))
                            {
                                var haveValue = selectItemDic[configField.FieldName].Count(x => x.Value.Equals(label)) > 0;
                                if (!haveValue)
                                {
                                    throw new Exception($"第{i}行，{configField.FieldDesc}，未匹配到此项的值：{label}");
                                }
                                else
                                {
                                    sheet.Cells[i, colIndex].Value = selectItemDic[configField.FieldName].FirstOrDefault(x => x.Value.Equals(label)).Key;
                                }
                            }
                        }
                        if (configField.InputBoxType == "select-multiple" && selectItemDic.ContainsKey(configField.FieldName))
                        {
                            var labelStr = sheet.Cells[i, colIndex]?.Value.ObjToString();
                            if (!string.IsNullOrWhiteSpace(labelStr))
                            {
                                var labels = labelStr.Trim().Split(new char[] { ',', '，' }, StringSplitOptions.RemoveEmptyEntries).Distinct().ToList();
                                var valList = new List<string>();
                                foreach (var label in labels)
                                {
                                    var haveValue = selectItemDic[configField.FieldName].Count(x => x.Value.Equals(label)) > 0;
                                    if (!haveValue)
                                    {
                                        throw new Exception($"第{i}行，{configField.FieldDesc}，未匹配到此项的值：{label}");
                                    }
                                    var value = selectItemDic[configField.FieldName].FirstOrDefault(x => x.Value.Equals(label)).Key;
                                    valList.Add(value);
                                }
                                sheet.Cells[i, colIndex].Value = valList.Distinct().JoinAsString(",");
                            }
                        }
                        if (configField.InputBoxType == "date")
                        {
                            var val = sheet.Cells[i, colIndex]?.Value.ObjToString();
                            if (!string.IsNullOrWhiteSpace(val) && !DateTime.TryParse(val, out _))
                            {
                                throw new Exception($"第{i}行，{configField.FieldDesc}，时间格式错误");
                            }
                        }

                        var cellValue = field.IS_NULLABLE == "YES" ? sheet.Cells[i, colIndex]?.Value ?? null :
                            (sheet.Cells[i, colIndex]?.Value ?? (string.IsNullOrEmpty(field.COLUMN_DEFAULT) ? "" : field.COLUMN_DEFAULT));

                        configField.FieldValue = CommonFieldHelper.GetTrueValueFromDbType(field?.DATA_TYPE ?? "VARCHAR", cellValue, field.COLUMN_DEFAULT);
                        colIndex++;
                    }

                    list.Add(configFieldList);
                }

                return list;
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Dispose();
            }
        }

        public void Dispose()
        {
            sheet?.Dispose();
        }
    }
}
